<%@page import="kau.dao.AttendInfoDAOImpl"%>
<%@page import="java.awt.Color"%>
<%@page import="kau.vo.Course"%>
<%@page import="kau.dao.CourseDAOImpl"%>
<%@page import="com.smartxls.*"%>
<%@page import="java.io.*"%>
<%@page import="java.util.Arrays"%>
<%@page import="kau.vo.AttendInfoAnalysis"%>
<%@page import="java.util.List"%>
<%@page import="java.util.ArrayList"%>
<%@page import="kau.vo.AttendInfo"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>

<%@ page import="java.net.URLDecoder"%>
<%@ page import="java.net.URLEncoder"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>

	<%
		File folder=new File("C:\\nems");
		if(!folder.exists())  //폴더가 없으면 생성
		folder.mkdirs();



		ArrayList<AttendInfo>[] periodicalAttendList =null;

		String periodical = (String)request.getAttribute("periodical");
		String attendAnalysis = request.getParameter("attendAnalysis");

		if(attendAnalysis != null){ // 출석정보분석
			ArrayList<AttendInfoAnalysis> attendInfoAnalysisList = (ArrayList) session.getAttribute("attendInfoAnalysisList");
			String courseName_ = (String)session.getAttribute("courseName");
			


			WorkBook workBook = new WorkBook(); 

			try{
		workBook.insertSheets(1,2);
		workBook.setSheet(0);
		workBook.setSheetName(0, courseName_+"출석분석");
		
		String month = request.getParameter("month");
		if(month.contains("-")){
			workBook.setText(1,0,month+" 출석분석");
		}
		else{
			workBook.setText(1,0,"전체출석분석");}
		
		RangeStyle rangeStyle = workBook.getRangeStyle(1, 0, 3, 3);//get format from range B2:C3
		rangeStyle.setMergeCells(true);//merge range
		//rangeStyle.MergeCells = false;//unmerge range
		
		//정렬
		rangeStyle.setHorizontalAlignment(RangeStyle.HorizontalAlignmentCenter); 
		rangeStyle.setVerticalAlignment(RangeStyle.VerticalAlignmentCenter);
		
		//글꼴 설정
		rangeStyle.setFontName("Arial");
		rangeStyle.setFontSize(14 * 20);
		rangeStyle.setFontColor(Color.blue.getRGB());
		rangeStyle.setFontItalic(true);
		rangeStyle.setFontBold(true);

		
		workBook.setRangeStyle(rangeStyle, 1, 0, 3, 3);//set format for range B2:C3
		
		
		workBook.setText(1,4,"강의명");
		workBook.setText(1,5,courseName_);
		workBook.setText(1,6,"강사명");
		Course course = CourseDAOImpl.getInstance().getCourse(Integer.parseInt(session.getAttribute("courseId").toString()));
		workBook.setText(1,7,course.getLecturer());
		workBook.setText(2,4,"시작일");
		workBook.setText(2,5,course.getStartDate());
		workBook.setText(2,6,"종료일");
		workBook.setText(2,7,course.getEndDate());
		workBook.setText(3,4,"시작시간");
		workBook.setText(3,5,course.getStartTime());
		workBook.setText(3,6,"종료시간");
		workBook.setText(3,7,course.getEndTime());
		
		
		
		workBook.setText(6,0,"아이디");
		workBook.setText(6,1,"이름");
		workBook.setText(6,2,"결석수");
		workBook.setText(6,3,"조퇴수");
		workBook.setText(6,4,"지각수");
		workBook.setText(6,5,"지각+조퇴수");
		workBook.setText(6,6,"정상출석수");
		workBook.setText(6,7,"감점");
		workBook.setText(6,8,"합계점수");
		
		
		workBook.setText(6,10,"총수업일수");
		workBook.setText(7,10,"출석일수");
		workBook.setText(8,10,"남은일수");
		workBook.setText(9,10,"진행률");
		workBook.setNumber(6,11,attendInfoAnalysisList.get(0).getAllAttendCnt());
		workBook.setNumber(7,11,attendInfoAnalysisList.get(0).getPastAttendCnt());
		workBook.setNumber(8,11,attendInfoAnalysisList.get(0).getLeftAttendCnt());
		workBook.setNumber(9,11,attendInfoAnalysisList.get(0).getProgress());
		
		
		for(int i=0; i<attendInfoAnalysisList.size(); i++){
			workBook.setText(i+7,0,attendInfoAnalysisList.get(i).getCustId());
			workBook.setText(i+7,1,attendInfoAnalysisList.get(i).getCustName());
			workBook.setNumber(i+7,2,attendInfoAnalysisList.get(i).getAbsenceCnt());
			workBook.setNumber(i+7,3,attendInfoAnalysisList.get(i).getEarlyLeavingCnt());
			workBook.setNumber(i+7,4,attendInfoAnalysisList.get(i).getLateCnt());
			workBook.setNumber(i+7,5,attendInfoAnalysisList.get(i).getEarlylateCnt());
			workBook.setNumber(i+7,6,attendInfoAnalysisList.get(i).getNomalCnt());
			workBook.setNumber(i+7,7,attendInfoAnalysisList.get(i).getAttendGrade());
			workBook.setNumber(i+7,8,attendInfoAnalysisList.get(i).getTotalGrade());
			
		}
		
		
		// 차트생성
		
		// 출석분석차트
		
		workBook.setSheet(1);
		workBook.setSheetName(1, "출석분석차트");
		
		int left = 0;
		int top = 0;
		int right =18;
		int bottom = 31;

		         //create chart with it's location
		        ChartShape chart = workBook.addChart(left,top,right,bottom);
		chart.setChartType(ChartShape.Column);
		         //link data source, link each series to columns(true to rows).
		        Integer temp = attendInfoAnalysisList.size()+7;
		         
		        chart.setLinkRange(courseName_+"출석분석!$B$7:$G$"+temp.toString(), false);
		        //chart.setSeriesXValueFormula(1, "출석분석!$B$7:$B$"+attendInfoAnalysisList.size()+7);


		        
		          //set series name
		chart.setSeriesName(0, "결석수");
		chart.setSeriesName(1, "조퇴수");
		chart.setSeriesName(2, "지각수");
		chart.setSeriesName(3, "지각+조퇴수");
		chart.setSeriesName(4, "정상출석수");
		
		chart.setTitle("출석분석차트");

		          //set plot area's color to darkgray
		          ChartFormat chartFormat = chart.getPlotFormat();
		          chartFormat.setSolid();
		          chartFormat.setForeColor(Color.WHITE.getRGB());
		          chart.setPlotFormat(chartFormat);

		          
		          //set series 0's color to RED
		          ChartFormat seriesformat = chart.getSeriesFormat(0);
		          seriesformat.setSolid();
		          seriesformat.setForeColor(Color.RED.getRGB());
		          chart.setSeriesFormat(0, seriesformat);

		          //set series 1's color to GREEN
		          seriesformat = chart.getSeriesFormat(1);
		          seriesformat.setSolid();
		          seriesformat.setForeColor(Color.GREEN.getRGB());
		          chart.setSeriesFormat(1, seriesformat);
		          
		          seriesformat = chart.getSeriesFormat(2);
		          seriesformat.setSolid();
		          seriesformat.setForeColor(Color.YELLOW.getRGB());
		          chart.setSeriesFormat(2, seriesformat);
		          
		          seriesformat = chart.getSeriesFormat(3);
		          seriesformat.setSolid();
		          seriesformat.setForeColor(Color.CYAN.getRGB());
		          chart.setSeriesFormat(3, seriesformat);

		          seriesformat = chart.getSeriesFormat(3);
		          seriesformat.setSolid();
		          seriesformat.setForeColor(Color.BLUE.getRGB());
		          chart.setSeriesFormat(3, seriesformat);

		          //set chart title's font property
		          ChartFormat titleformat = chart.getTitleFormat();
		          titleformat.setFontSize(14*20);
		          titleformat.setFontUnderline(true);
		          chart.setTitleFormat(titleformat);


		          
		          // 출석분석차트
		          
		      	workBook.setSheet(2);
		workBook.setSheetName(2, "합계점수차트");
		
			

		         //create chart with it's location
		        ChartShape chart2 = workBook.addChart(left,top,right,bottom);
		chart2.setChartType(ChartShape.Column);
		         //link data source, link each series to columns(true to rows).
		        
		        chart2.setLinkRange(courseName_+"출석분석!$B$7:$B$"+temp.toString()+","+courseName_+"출석분석!$I$7:$I$"+temp.toString(),false);
		       // chart2.setLinkRange(,false);
		       // chart2.setSeriesXValueFormula(0, "출석분석!$B$7:$B$"+temp.toString());


		        
		          //set series name
		chart2.setSeriesName(0, "합계점수");
		
		chart2.setTitle("합계점수차트");

		          //set plot area's color to darkgray
		           ChartFormat  chartFormat2 = chart2.getPlotFormat();
		          chartFormat2.setSolid();
		          chartFormat2.setForeColor(Color.WHITE.getRGB());
		          chart2.setPlotFormat(chartFormat2);

		          //set series 0's color to blue
		          ChartFormat  seriesformat2 = chart2.getSeriesFormat(0);
		          seriesformat2.setSolid();
		          seriesformat2.setForeColor(Color.RED.getRGB());
		          chart2.setSeriesFormat(0, seriesformat2);

		       
		          //set chart title's font property
		           ChartFormat titleformat2 = chart2.getTitleFormat();
		          titleformat2.setFontSize(14*20);
		          titleformat2.setFontUnderline(true);
		          chart2.setTitleFormat(titleformat2);
			
			}
			catch(Exception e){
		e.printStackTrace();
			}
			workBook.write("c:\\nems\\output.xls");

			////////////////////////////////////////////////////////////////////////////////////////////////

			response.reset();
			response.setContentType("application/octer-stream");
			// 일반파라미터 application/x-www-form-urlencoded 디폴트로 설정 되있다!
			// 파일은 다르기 때문에 response 값을 바꾼것임

			// 해더의 이름에 내가 원하는 전송할 파일 이름을 넣은 것임
			// 파일이름은 브라우저 설정 문자셋으로 바뀐것으로 넣는다! 
			response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(courseName_+"_출석분석"+".xls", "utf-8") + ";");
		}
		else if(periodical == null && attendAnalysis ==null){// 개인출석정보
			ArrayList<AttendInfo> attendInfoList = (ArrayList)request.getAttribute("attendInfoList");
			String custId_= attendInfoList.get(0).getCustId();
			String custName_= attendInfoList.get(0).getCustName();
			String courseName_ = attendInfoList.get(0).getCourseName();
			WorkBook workBook = new WorkBook(); 

			try{
		workBook.setSheet(0);
		workBook.setSheetName(0, courseName_+"_"+custId_);
		
		workBook.setText(0,0,courseName_+"_"+custName_+"출석정보");
			

		workBook.setText(3,0,"아이디");
		workBook.setText(3,1,"이름");
		workBook.setText(3,2,"코스명");
		workBook.setText(3,3,"날짜");
		workBook.setText(3,4,"체크인시간");
		workBook.setText(3,5,"체크아웃시간");
		workBook.setText(3,6,"출결정보");
		
		for(int i=0;i<attendInfoList.size();i++){
			workBook.setText(i+4,0,attendInfoList.get(i).getCustId());
			workBook.setText(i+4,1,attendInfoList.get(i).getCustName());
			workBook.setText(i+4,2,attendInfoList.get(i).getCourseName());
			workBook.setText(i+4,3,attendInfoList.get(i).getDay());
			workBook.setText(i+4,4,attendInfoList.get(i).getCheckInTime());
			workBook.setText(i+4,5,attendInfoList.get(i).getCheckOutTime());
			workBook.setText(i+4,6,attendInfoList.get(i).getState());
		}
		
		workBook.setText(3,9,"수업일수");
		workBook.setText(4,9,"결석수");
		workBook.setText(5,9,"지각수");
		workBook.setText(6,9,"조퇴수");
		workBook.setText(7,9,"지각+조퇴수");
		workBook.setText(8,9,"정상출석수");
		Integer size = attendInfoList.size()+4;
		workBook.setFormula(4, 10, "COUNTA(G5:G"+size.toString()+")");
		workBook.setFormula(4, 10, "COUNTIF(G5:G"+size.toString()+",\"결석\")");
		workBook.setFormula(5, 10, "COUNTIF(G5:G"+size.toString()+",\"지각\")");
		workBook.setFormula(6, 10, "COUNTIF(G5:G"+size.toString()+",\"조퇴\")");
		workBook.setFormula(7, 10, "COUNTIF(G5:G"+size.toString()+",\"지각+조퇴\")");
		workBook.setFormula(8, 10, "COUNTIF(G5:G"+size.toString()+",\"정상\")");
		
		
		ChartShape chart2 = workBook.addChart(9,10,18,30);
		chart2.setChartType(ChartShape.Column);
		 
		chart2.setLinkRange("$J$5:$K9",false);
		    


		        
		          //set series name
		chart2.setSeriesName(0, "횟수");
		
		chart2.setTitle("출석분포표");

		          //set plot area's color to darkgray
		           ChartFormat  chartFormat2 = chart2.getPlotFormat();
		          chartFormat2.setSolid();
		          chartFormat2.setForeColor(Color.WHITE.getRGB());
		          chart2.setPlotFormat(chartFormat2);

		          //set series 0's color to blue
		          ChartFormat  seriesformat2 = chart2.getSeriesFormat(0);
		          seriesformat2.setSolid();
		          seriesformat2.setForeColor(Color.BLUE.getRGB());
		          chart2.setSeriesFormat(0, seriesformat2);

		       
		          //set chart title's font property
		           ChartFormat titleformat2 = chart2.getTitleFormat();
		          titleformat2.setFontSize(14*20);
		          titleformat2.setFontUnderline(true);
		          chart2.setTitleFormat(titleformat2);
		
		
		
		
		
		
		
		
		workBook.write("c:\\nems\\output.xls");
			}catch(Exception e){
		e.printStackTrace();
			}

			////////////////////////////////////////////////////////////////////////////////////////////////

			response.reset();
			response.setContentType("application/octer-stream");
			// 일반파라미터 application/x-www-form-urlencoded 디폴트로 설정 되있다!
			// 파일은 다르기 때문에 response 값을 바꾼것임

			// 해더의 이름에 내가 원하는 전송할 파일 이름을 넣은 것임
			// 파일이름은 브라우저 설정 문자셋으로 바뀐것으로 넣는다! 
			response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(courseName_+"_"+custName_+".xls", "utf-8") + ";");
		}



		//기간별
		else if(periodical!=null){ //날짜기간별 조회

			String startDate_ = (String)request.getAttribute("startDate");
			String endDate_ = (String)request.getAttribute("endDate");
			ArrayList<AttendInfo> attendInfoList = (ArrayList)request.getAttribute("attendInfoList");
			List dayList = (List) request.getAttribute("dayList");
			WorkBook workBook = new WorkBook(); 
			
	        
			try{
		workBook.insertSheets(1,dayList.size()-1);
		periodicalAttendList = new ArrayList[dayList.size()];
		for(int i=0; i<dayList.size();i++)
		{
			workBook.setSheet(i);
			workBook.setSheetName(i, dayList.get(i).toString());
			workBook.setText(0,0,dayList.get(i)+"출석분석");
			
			workBook.setText(3,0,"아이디");
			workBook.setText(3,1,"이름");
			workBook.setText(3,2,"코스명");
			workBook.setText(3,3,"날짜");
			workBook.setText(3,4,"체크인시간");
			workBook.setText(3,5,"체크아웃시간");
			workBook.setText(3,6,"출결정보");
			
			periodicalAttendList[i] = new ArrayList<AttendInfo>();
			for(int j=0;j<attendInfoList.size();j++){
		
		if(dayList.get(i).toString().equals(attendInfoList.get(j).getDay().toString())){
			periodicalAttendList[i].add(attendInfoList.get(j));
		}
			}
		}
		
		//custId= new Label(0,1,periodicalAttendList.length);
		
		//sheetList[0].addCell(custId);
		
		// periodicalAttendList[] : 요일별로 AttendInfo가 저장되있는배열
		for(int i=0;i<periodicalAttendList.length;i++)	// length =3
		{
			workBook.setSheet(i);
			for(int j=0;j<periodicalAttendList[i].size();j++){
				workBook.setText(j+4,0,periodicalAttendList[i].get(j).getCustId());
				workBook.setText(j+4,1,periodicalAttendList[i].get(j).getCustName());
				workBook.setText(j+4,2,periodicalAttendList[i].get(j).getCourseName());
				workBook.setText(j+4,3,periodicalAttendList[i].get(j).getDay());
				workBook.setText(j+4,4,periodicalAttendList[i].get(j).getCheckInTime());
				workBook.setText(j+4,5,periodicalAttendList[i].get(j).getCheckOutTime());
				workBook.setText(j+4,6,periodicalAttendList[i].get(j).getState());
			}
			
		}
		
		for(int i=0;i<periodicalAttendList.length;i++){
			workBook.setSheet(i);

			workBook.setText(3,9,"총인원수");
			workBook.setText(4,9,"결석수");
			workBook.setText(5,9,"지각수");
			workBook.setText(6,9,"조퇴수");
			workBook.setText(7,9,"지각+조퇴수");
			workBook.setText(8,9,"정상출석수");
			Integer size = (Integer)periodicalAttendList[i].size()+4;
			
			workBook.setFormula(3, 10, "COUNTA(G5:G"+size.toString()+")");
			workBook.setFormula(4, 10, "COUNTIF(G5:G"+size.toString()+",\"결석\")");
			workBook.setFormula(5, 10, "COUNTIF(G5:G"+size.toString()+",\"지각\")");
			workBook.setFormula(6, 10, "COUNTIF(G5:G"+size.toString()+",\"조퇴\")");
			workBook.setFormula(7, 10, "COUNTIF(G5:G"+size.toString()+",\"지각+조퇴\")");
			workBook.setFormula(8, 10, "COUNTIF(G5:G"+size.toString()+",\"정상\")");
			
			
			ChartShape chart_periodical = workBook.addChart(9,13,17,27);
			chart_periodical.setChartType(ChartShape.Column);
		         //link data source, link each series to columns(true to rows).
		        
		    chart_periodical.setLinkRange("$J$5:$K$9",false);
		       
		          //set series name
			chart_periodical.setSeriesName(0, "횟수");
			
			chart_periodical.setTitle("출석분포표");

          ChartFormat chartFormat = chart_periodical.getPlotFormat();
          chartFormat.setSolid();
          chartFormat.setForeColor(Color.WHITE.getRGB());
          chart_periodical.setPlotFormat(chartFormat);

          //set series 0's color to blue
          ChartFormat  seriesformat_periodical = chart_periodical.getSeriesFormat(0);
          seriesformat_periodical.setSolid();
          seriesformat_periodical.setForeColor(Color.BLUE.getRGB());
          chart_periodical.setSeriesFormat(0, seriesformat_periodical);

          //set chart title's font property
           ChartFormat titleformat_periodical = chart_periodical.getTitleFormat();
           titleformat_periodical.setFontSize(14*20);
           titleformat_periodical.setFontUnderline(true);
          chart_periodical.setTitleFormat(titleformat_periodical);
			
			
		}
		
		
		
		
		
			}catch(Exception e){
		
			}

			workBook.write("c:\\nems\\output.xls");
			String courseName_ = attendInfoList.get(0).getCourseName();
			

			////////////////////////////////////////////////////////////////////////////////////////////////

			response.reset();
			response.setContentType("application/octer-stream");
			// 일반파라미터 application/x-www-form-urlencoded 디폴트로 설정 되있다!
			// 파일은 다르기 때문에 response 값을 바꾼것임

			// 해더의 이름에 내가 원하는 전송할 파일 이름을 넣은 것임
			// 파일이름은 브라우저 설정 문자셋으로 바뀐것으로 넣는다! 
			response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(courseName_+"_"+startDate_+"_"+endDate_+".xls", "utf-8") + ";");
			
		}
		response.setHeader("Content-Transper-Encoding", "binary");
		// 사이즈도 알려줘야 한다
		File file = new File("C:\\nems","output.xls");

		response.setContentLength((int)file.length());
		// cache에 안 넣을 거다! 
		response.setHeader("Pargma", "no-cache");
		// Expires 는 또 무슨 뜻인가염 넹?!
		response.setHeader("Expires", "-1");

		byte[] data = new byte[1024 * 1024];
		BufferedInputStream fis = new BufferedInputStream(new FileInputStream(file));
		// response가 사용하는 OutputStream()을 out 스트림으로 사용 
		BufferedOutputStream fos = new BufferedOutputStream(response.getOutputStream());

		int count = 0;
		while((count = fis.read(data)) != -1){
		 fos.write(data);
		}

		if(fis != null) fis.close();
		if(fos != null) fos.close();

		out.clear();
		out = pageContext.pushBody();
	%>
</body>
</html>